﻿/************************************************************/
/*****	Keydance Articles Install Script     			*****/
/*****  ================================        		*****/
/*****                                                  *****/
/*****  Version:	Install Script             			*****/
/*****	Requires:	DotNetNuke 4.6.0 or above			*****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

/* Audit Table */
/*****************/

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_AuditInfo]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}[{objectQualifier}Toolkit_AuditInfo]
			(
				[AuditInfoID] [int] IDENTITY(1,1) NOT NULL,
				[AuditTable] [nvarchar](100) NOT NULL,
				[ItemID] [int] NOT NULL,
				[VersionNo] [int] NOT NULL CONSTRAINT [DF_{objectQualifier}Toolkit_AuditInfo_VersionNo]  DEFAULT ((1)),
				[AuditType] [int] NOT NULL,
				[Comment] [ntext] NULL,
				[AuditUser] [int] NOT NULL CONSTRAINT [DF_{objectQualifier}Toolkit_AuditInfo_AuditUser]  DEFAULT ((0)),
				[AuditDate] [datetime] NOT NULL,
				CONSTRAINT [PK_{objectQualifier}Toolkit_AuditInfo] PRIMARY KEY CLUSTERED ( [AuditInfoID] ASC )
			)

		CREATE NONCLUSTERED INDEX IX_{objectQualifier}Toolkit_AuditInfo ON dbo.{objectQualifier}Toolkit_AuditInfo ( AuditTable, ItemID )
	END

GO

/* Extensions Table */
/********************/

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_Extensions]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}[{objectQualifier}Toolkit_Extensions]
			(
				[ExtensionID] [int] IDENTITY(1,1) NOT NULL,
				[PackageID] [int] NOT NULL,
				[DesktopModuleID] [int] NOT NULL,
				[ExtensionsFolder] [nvarchar](250) NOT NULL,
				[ExtensionName] [nvarchar](50) NOT NULL,
				[IExtensionClass] [nvarchar](250) NOT NULL,
				CONSTRAINT [PK_{objectQualifier}Toolkit_Extensions] PRIMARY KEY CLUSTERED ( [ExtensionID] ASC )
			)
	END

GO

/* Skins Table */
/***************/

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_Skins]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}[{objectQualifier}Toolkit_Skins]
			(
				[SkinID] [int] IDENTITY(1,1) NOT NULL,
				[PackageID] [int] NOT NULL,
				[DesktopModuleID] [int] NOT NULL,
				[SkinsFolder] [nvarchar](250) NOT NULL,
				[SkinName] [nvarchar](50) NOT NULL,
				CONSTRAINT [PK_{objectQualifier}Toolkit_Skins] PRIMARY KEY CLUSTERED ( [SkinID] ASC )
)	END

GO

/* CreateExtension */
/*******************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_CreateExtension]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_CreateExtension]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_CreateExtension]
(
	@PackageID					int,
	@DesktopModuleID			int,
	@ExtensionsFolder			nvarchar (250),
	@ExtensionName				nvarchar (50),
	@IExtensionClass			nvarchar (250)
)
AS
	INSERT INTO {objectQualifier}Toolkit_Extensions	
		(
			PackageID,
			DesktopModuleID,
			ExtensionsFolder,
			ExtensionName,
			IExtensionClass
		)
		VALUES		
			(
				@PackageID,
				@DesktopModuleID,
				@ExtensionsFolder,
				@ExtensionName,
				@IExtensionClass
			)

	SELECT @@IDENTITY

GO

/* DeleteExtension */
/*******************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_DeleteExtension]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_DeleteExtension]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_DeleteExtension]
(
	@ExtensionID	int
)
AS
	DELETE FROM {objectQualifier}Toolkit_Extensions
		WHERE ExtensionID = @ExtensionID

GO

/* GetExtension */
/****************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_GetExtension]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetExtension]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetExtension]
(
	@ExtensionID	int
)
AS
	SELECT *
		FROM {objectQualifier}Toolkit_Extensions
		WHERE ExtensionID = @ExtensionID
		ORDER BY ExtensionName
GO

/* GetExtensionByName */
/**********************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_GetExtensionByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetExtensionByName]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetExtensionByName]
(
	@DesktopModuleID	int,
	@ExtensionName			nvarchar(50)
)
AS
SELECT *
	FROM {objectQualifier}Toolkit_Extensions
	WHERE @DesktopModuleID = @DesktopModuleID
		AND ExtensionName = @ExtensionName

GO

/* GetExtensionByPackageID */
/***************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_GetExtensionByPackageID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetExtensionByPackageID]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetExtensionByPackageID]
(
	@PackageID	int
)
AS
SELECT *
	FROM {objectQualifier}Toolkit_Extensions
	WHERE @PackageID = @PackageID

GO

/* GetExtensions */
/*****************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_GetExtensions]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetExtensions]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetExtensions]
(
	@DesktopModuleID	int
)
AS
	SELECT *
		FROM {objectQualifier}Toolkit_Extensions
		WHERE DesktopModuleID = @DesktopModuleID
		ORDER BY ExtensionName
GO

/* CreateSkin */
/**************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_CreateSkin]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_CreateSkin]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_CreateSkin]
(
	@PackageID				int,
	@DesktopModuleID		int,
	@SkinsFolder			nvarchar (250),
	@SkinName				nvarchar (50)
)
AS
	INSERT INTO {objectQualifier}Toolkit_Skins	
		(
			PackageID,
			DesktopModuleID,
			SkinsFolder,
			SkinName
		)
		VALUES		
			(
				@PackageID,
				@DesktopModuleID,
				@SkinsFolder,
				@SkinName
			)

	SELECT @@IDENTITY

GO

/* DeleteSkin */
/**************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_DeleteSkin]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_DeleteSkin]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_DeleteSkin]
(
	@SkinID	int
)
AS
	DELETE FROM {objectQualifier}Toolkit_Skins
		WHERE SkinID = @SkinID

GO

/* GetSkin */
/***********/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_GetSkin]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetSkin]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetSkin]
(
	@SkinID	int
)
AS
	SELECT *
		FROM {objectQualifier}Toolkit_Skins
		WHERE SkinID = @SkinID
		ORDER BY SkinName
GO

/* GetSkinByName */
/*****************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_GetSkinByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetSkinByName]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetSkinByName]
(
	@DesktopModuleID	int,
	@SkinName			nvarchar(50)
)
AS
SELECT *
	FROM {objectQualifier}Toolkit_Skins
	WHERE @DesktopModuleID = @DesktopModuleID
		AND SkinName = @SkinName

GO

/* GetSkinByPackageID */
/**********************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_GetSkinByPackageID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetSkinByPackageID]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetSkinByPackageID]
(
	@PackageID	int
)
AS
SELECT *
	FROM {objectQualifier}Toolkit_Skins
	WHERE @PackageID = @PackageID

GO

/* GetSkins */
/************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_GetSkins]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetSkins]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetSkins]
(
	@DesktopModuleID	int
)
AS
	SELECT *
		FROM {objectQualifier}Toolkit_Skins
		WHERE DesktopModuleID = @DesktopModuleID
		ORDER BY SkinName
GO

/* CreateAuditInfo */
/*******************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo]
(
	@AuditTable		nvarchar (100),
	@ItemID			int,
	@VersionNo		int,
	@AuditType		int,
	@Comment		ntext,
	@AuditUserID	int
)
AS
	INSERT INTO dbo.{objectQualifier}Toolkit_AuditInfo	
		(
			AuditTable,
			ItemID,
			VersionNo,
			AuditType,
			Comment,
			AuditUser,
			AuditDate
		)
		VALUES			
			(
				@AuditTable,
				@ItemID,
				@VersionNo,
				@AuditType,
				@Comment,
				@AuditUserID,
				getdate()
			)

GO

/* GetAuditHistory */
/*******************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Toolkit_GetAuditHistory]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetAuditHistory]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}Toolkit_GetAuditHistory]
(
	@AuditTable nvarchar(100),
	@ItemID		int
)
AS
	SELECT 
		i.ItemID,
		i.VersionNo,
		t.AuditType,
		i.Comment,
		u.DisplayName,
		i.AuditDate
	FROM {objectQualifier}Toolkit_AuditInfo i
		INNER JOIN {objectQualifier}Toolkit_AuditType t ON i.AuditType = t.AuditTypeID
		INNER JOIN {objectQualifier}Users u On i.AuditUser = u.UserID
		WHERE AuditTable = @AuditTable
			AND ItemID = @ItemID

GO

/* Add Package Type for Skins */
/******************************/

IF (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}PackageTypes WHERE PackageType = 'Toolkit_Module_Skins' ) = 0
	BEGIN
		INSERT INTO {databaseOwner}{objectQualifier}PackageTypes
			( PackageType, Description, SecurityAccessLevel )
			VALUES ( 'Toolkit_Module_Skins', 'Module Skins', 3 )
	END
GO

/* Add SkinInstaller for Skins */
/******************************/

IF (SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}Lists WHERE ListName = 'Installer' AND Value = 'Toolkit_Module_Skin' ) = 0
	BEGIN
		INSERT INTO {databaseOwner}{objectQualifier}Lists
			( ListName, Value, [Text] )
			VALUES ( 'Installer', 'Toolkit_Module_Skin', 'DotNetNuke.ModuleToolkit.Skinning.ModuleSkinInstaller' )
	END
GO

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/
